package utilityTools;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public class ExcelUtility {
    private String filePath = "";
    private XSSFWorkbook workBookHard = null;
    //一般操作的是下面这个对象，但是它只会加载一部分数据到内存，其余数据会持久化在本地磁盘
    //操作很久之后发现SXSSF为空，这是因为数据都存放在XSSF中，你需要重新获取一下
    private SXSSFWorkbook sworkBookRAM = null;
    private Sheet curSheet = null;

    public ExcelUtility(String filePath)
    {
        this.filePath = filePath;
    }

    /**
     * 打开待读取/写入的Excel文件
     * @throws FileNotFoundException
     */
    private void openExcel() throws FileNotFoundException {
        FileInputStream inFile = new FileInputStream(filePath);
        try {
            workBookHard = new XSSFWorkbook(inFile);
            //第二个参数表示：只保留1000条数据在内存，其余的写入硬盘
            sworkBookRAM = new SXSSFWorkbook(workBookHard, 1000);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                inFile.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 打开指定的sheet页，作为输入/读取
     * @param sheetId sheet的唯一标识（可以是ID或者名字）
     * @param <T>
     * @return true-成功
     */
    private <T> boolean selectSheet(T sheetId) {
        if (sheetId instanceof String) {
            curSheet = sworkBookRAM.getSheet(sheetId.toString());
            return true;
        } else if (sheetId instanceof Integer) {
            curSheet = sworkBookRAM.getSheetAt(((Integer) sheetId).intValue());
            return true;
        } else {
            return false;
        }
    }

    /**
     * 向Excel中批量写入数据
     * @param sheetNo sheet编号（从1开始）
     * @param startRow 开始写数据的起始行
     * @param dataList 数据列表(内部是列集合，外部是行集合)
     * @param <T>
     * @return true-写入成功
     * @throws FileNotFoundException
     */
    public <T> boolean writeDatas(int sheetNo, int startRow, List<List<T>> dataList) throws FileNotFoundException {
        //1、打开excel文件
        openExcel();
        //2、选择写入数据的sheet页
        boolean result = selectSheet(sheetNo);
        if (result == false) {
            return result;
        }
        //3、逐行写入数据
        for (List<T> data : dataList) {
            writeSingleRowData(startRow++, data);
        }
        //4、保存文件，关闭流
        saveExcel();
        return true;
    }

    /**
     * 写一行数据到Excel中
     * @param rowNum 行号
     * @param valueList 一行的内容
     * @param <T>
     * @return true-写入成功
     */
    private <T> boolean writeSingleRowData(int rowNum, List<T> valueList) {
        Row row = curSheet.getRow(rowNum);
        if (row == null) {
            row = curSheet.createRow(rowNum);
        }

        if (row == null) {
            return false;
        }

        for (int cellNum = 0; cellNum < valueList.size(); ++cellNum) {
            Cell cell = row.getCell(cellNum);
            if (cell == null) {
                cell = row.createCell(cellNum);
            }

            if (cell == null) {
                continue;
            }

            T value = valueList.get(cellNum);
            cell.setCellValue(String.valueOf(value));
        }

        return true;
    }

    /**
     * 保存数据到Excel
     * @throws FileNotFoundException
     */
    private void saveExcel() throws FileNotFoundException {
        FileOutputStream out = new FileOutputStream(filePath);
        try {
            sworkBookRAM.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        sworkBookRAM.dispose();
    }
}
